﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using DTO;

namespace DAO
{
    public class TuKhoaDiaDiemDAO : DataProvider
    {
        protected override object GetDataRowFromDataReader(OleDbDataReader dr)
        {
            TuKhoaDiaDiemDTO tuKhoaDiaDiemDTO = new TuKhoaDiaDiemDTO();
            if (!dr.IsDBNull(0))
            {
                tuKhoaDiaDiemDTO.MaTuKhoaTenDiaDiem = dr.GetInt32(0);
            }

            if (!dr.IsDBNull(1))
            {
                tuKhoaDiaDiemDTO.TuKhoaTenDiaDiem = dr.GetString(1);
            }

            if (!dr.IsDBNull(2))
            {
                tuKhoaDiaDiemDTO.MaTenDiaDiem = dr.GetInt32(2);
            }

            return (object)tuKhoaDiaDiemDTO;
        }

        public List<TuKhoaDiaDiemDTO> Search(string strInput)
        {
            List<TuKhoaDiaDiemDTO> lstTuKhoaDiaDiemDTO = new List<TuKhoaDiaDiemDTO>();
            try
            {
                strInput = strInput.Replace(@"'", @"''");
                //string strSQL = @"SELECT *" +
                //                @" FROM TUKHOADIADIEM" +
                //                @" WHERE '" + strInput + @"' LIKE " + @"'*' + TuKhoaTenDiaDiem + '*'";
                //string strSQL = @"SELECT * FROM TUKHOADIADIEM WHERE INSTR('" + strInput + @"', TuKhoaTenDiaDiem) > 0";
                string strSQL = String.Format(
                    "SELECT * FROM TUKHOADIADIEM WHERE INSTR('{0}', TuKhoaTenDiaDiem) > 0",
                    strInput);

                using (OleDbDataReader dr = this.ExcuteReader(strSQL))
                {
                    TuKhoaDiaDiemDTO tuKhoaDiaDiemDTO = null;
                    while (dr.Read())
                    {
                        tuKhoaDiaDiemDTO = (TuKhoaDiaDiemDTO)GetDataRowFromDataReader(dr);
                        lstTuKhoaDiaDiemDTO.Add(tuKhoaDiaDiemDTO);
                    }
                }

                if (FLAG)
                {
                    if (0 == lstTuKhoaDiaDiemDTO.Count)
                    {
                        strSQL = String.Format(
                            "SELECT * FROM TUKHOADIADIEM WHERE INSTR(TuKhoaTenDiaDiem, '{0}') > 0",
                            strInput);

                        using (OleDbDataReader dr = this.ExcuteReader(strSQL))
                        {
                            TuKhoaDiaDiemDTO tuKhoaDiaDiemDTO = null;
                            while (dr.Read())
                            {
                                tuKhoaDiaDiemDTO = (TuKhoaDiaDiemDTO)GetDataRowFromDataReader(dr);
                                lstTuKhoaDiaDiemDTO.Add(tuKhoaDiaDiemDTO);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                lstTuKhoaDiaDiemDTO = new List<TuKhoaDiaDiemDTO>();
            }

            return lstTuKhoaDiaDiemDTO;
        }
        public List<TuKhoaDiaDiemDTO> AutoCompleteTuKhoaDiaDiem(String strInput)
        {
            List<TuKhoaDiaDiemDTO> lstTuKhoaDiaDiem = new List<TuKhoaDiaDiemDTO>();
            try
            {
                string strSQL = @"SELECT DISTINCT TOP 10 TuKhoaTenDiaDiem" +
                                @" FROM TUKHOADIADIEM" +
                                @" WHERE TuKhoaTenDiaDiem LIKE '%" + strInput +
                                @"%'";
                OleDbDataReader dr = this.ExcuteReader(strSQL);

                TuKhoaDiaDiemDTO tuKhoaDiaDiemDTO = null;

                while (dr.Read())
                {
                    tuKhoaDiaDiemDTO = new TuKhoaDiaDiemDTO();
                    tuKhoaDiaDiemDTO.TuKhoaTenDiaDiem = dr.GetString(0);
                    lstTuKhoaDiaDiem.Add(tuKhoaDiaDiemDTO);
                }


            }
            catch (Exception ex)
            {
                lstTuKhoaDiaDiem = new List<TuKhoaDiaDiemDTO>();
            }
            return lstTuKhoaDiaDiem;
        }
        public List<TuKhoaDiaDiemDTO> AutoCompleteTuKhoaDiaDiemTheoDichVu(String strInput, int MaDichVu)
        {
            List<TuKhoaDiaDiemDTO> lstTuKhoaDiaDiem = new List<TuKhoaDiaDiemDTO>();
            try
            {
                string strSQL = @"SELECT DISTINCT TOP 10 TUKHOADIADIEM.TuKhoaTenDiaDiem" +
                                @" FROM TUKHOADIADIEM , TENDIADIEM , DULIEU" +
                                @" WHERE TUKHOADIADIEM.TuKhoaTenDiaDiem LIKE '%" + strInput +
                                @"%' AND " +
                                @"TUKHOADIADIEM.MaTenDiaDiem = TENDIADIEM.MaTenDiaDiem AND " +
                                @"TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem AND " +
                                @"DULIEU.MaDichVu = " + MaDichVu.ToString();
                OleDbDataReader dr = this.ExcuteReader(strSQL);

                TuKhoaDiaDiemDTO tuKhoaDiaDiemDTO = null;

                while (dr.Read())
                {
                    tuKhoaDiaDiemDTO = new TuKhoaDiaDiemDTO();
                    tuKhoaDiaDiemDTO.TuKhoaTenDiaDiem = dr.GetString(0);
                    // tenDiaDiemDTO.MaTenDiaDiem =  dr.GetInt32(1);
                    lstTuKhoaDiaDiem.Add(tuKhoaDiaDiemDTO);
                }

            }
            catch (Exception ex)
            {
                lstTuKhoaDiaDiem = new List<TuKhoaDiaDiemDTO>();
            }
            return lstTuKhoaDiaDiem;
        }
    }
}
